import mysql.connector
from mysql.connector import Error


class MySQLDatabase:
    def __init__(self, config):
        self.config = config
        self.cnx = None
        self.cursor = None

    def connect(self):
        try:
            self.cnx = mysql.connector.connect(**self.config)
            self.cursor = self.cnx.cursor()
        except Error as e:
            print(f"Error connecting to MySQL Database: {e}")


    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.cnx:
            self.cnx.close()

    def insert(self, table, data):
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        try:
            self.cursor.execute(query, list(data.values()))
            self.cnx.commit()
        except Error as e:
            print(f"Error inserting data into table {table}: {e}")

    def insert_many(self, table, data_list):
        columns = ', '.join(data_list[0].keys())
        placeholders = ', '.join(['%s'] * len(data_list[0]))
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        try:
            self.cursor.executemany(query, [list(d.values()) for d in data_list])
            self.cnx.commit()
        except Error as e:
            print(f"Error inserting multiple rows into table {table}: {e}")

    def delete(self, table, condition):
        query = f"DELETE FROM {table} WHERE {condition}"
        try:
            self.cursor.execute(query)
            self.cnx.commit()
        except Error as e:
            print(f"Error deleting from table {table}: {e}")

    def delete_many(self, table, conditions):
        try:
            for condition in conditions:
                self.delete(table, condition)
            self.cnx.commit()
        except Error as e:
            print(f"Error deleting multiple rows from table {table}: {e}")

    def update(self, table, data, condition):
        updates = ', '.join([f"{key} = %s" for key in data])
        query = f"UPDATE {table} SET {updates} WHERE {condition}"
        try:
            self.cursor.execute(query, list(data.values()))
            self.cnx.commit()
        except Error as e:
            print(f"Error updating table {table}: {e}")

    def select(self, table, columns="*", condition=None):
        if condition:
            query = f"SELECT {columns} FROM {table} WHERE {condition}"
        else:
            query = f"SELECT {columns} FROM {table}"
        try:
            self.cursor.execute(query)
            result = self.cursor.fetchall()
            return result
        except Error as e:
            print(f"Error selecting from table {table}: {e}")
            return None

    def select_auto(self, table, columns="*", condition=None):
        if condition:
            query = f"SELECT {columns} FROM {table} WHERE {condition}"
        else:
            query = f"SELECT {columns} FROM {table}"
        try:
            self.cursor.execute(query)
            result = self.cursor.fetchall()
            return result
        except Error as e:
            print(f"Error selecting from table {table}: {e}")
            return None
# 使用示例
config = {
    'user': 'root',
    'password': 'jsti@2024',
    'host': '192.168.110.235',
    'port': '8306',
    'database': 'bid',
}
# 全局数据库连接对象
# global_db_connection = create_connection()
# db = MySQLDatabase(config)
# db.connect()

# 插入单条数据
# db.insert('bid_source_data', {
#     'project_name': '项目名称',
#     'company': '所属单位',
#     'area': '所属地区',
#     'city': '市',
#     'district': '区'
# })

# 插入多条数据
# db.insert_many('bid_source_data', [
#     {'project_name': '项目名称1', 'company': '所属单位1', 'area': '所属地区1'},
#     {'project_name': '项目名称2', 'company': '所属单位2', 'area': '所属地区2'},
# ])

# 删除单条数据
# db.delete('bid_source_data', "id = 1")

# 删除多条数据
# db.delete_many('bid_source_data', ["id = 2", "id = 3"])

# 更新数据
# db.update('bid_source_data', {'project_name': '更新后的项目名称'}, "id = 4")

# 查询数据
# results = db.select('bid_source_data', condition="id > 5")
# for row in results:
#     print(row)
#
# db.close()
